package com.oocl.task20170718.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.oocl.task20170718.dao.BookDao;
import com.oocl.task20170718.entity.Book;
import com.oocl.task20170718.util.DBUtil;

public class BookDaoImpl implements BookDao {
	
	public int add(Book bk) {
		String sql =
				" insert into BOOKS                               "+
				" (id,name,pub_date,author,price,is_new,publisher)"+
				" values                                          "+
				" (book_id_seq.nextval,?,?,?,?,?,?)               ";
		Connection con=null;
		PreparedStatement pst = null;
		int m = 0;
		try {
			con=DBUtil.createConnection();
			pst = con.prepareStatement(sql);

			//idx form 1
			pst.setString(1, bk.getName());
			pst.setDate(2, new java.sql.Date(bk.getPublishTime().getTime()));
			pst.setString(3, bk.getAuthor());
			pst.setDouble(4, bk.getPrice());
			pst.setBoolean(5, !bk.getOld());
			pst.setString(6, bk.getPublisher());
			
			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
//			DBUtil.close(null, pst, null);
			DBUtil.close(con, pst, null);
		}
		return m;
	}

	public int delete(Integer id) {
		String sql = "delete from books bk where bk.id=?";
		Connection con=null;
		PreparedStatement pst = null;
		int m = 0;
		try {
			con=DBUtil.createConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, id);
			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		}
		return m;
	}

	public int update(Integer id, Book bk) {
		String sql = null;
		String firstSql =
				" update books bk " +
				" set             " ;
		String secondSql = null;
		String thirdSql =
				" where bk.id=10 " ;
		StringBuilder buf=new StringBuilder();
		List<String> updateAttrs=new ArrayList<String>();
		Connection con=null;
		PreparedStatement pst = null;
		int m = 0;
		try {
			decideUpdateAttrs(bk,updateAttrs,buf);
			con=DBUtil.createConnection();
			
			char[] dst=new char[buf.length()-1];
			buf.getChars(0, dst.length, dst, 0);
			secondSql=new String(dst);
			
//			System.out.println( "updateAttrs:"  );
//			System.out.println( updateAttrs  );
			
			sql = firstSql +" "+ secondSql +" "+ thirdSql;
			
//			System.out.println( sql );
			pst = con.prepareStatement(sql);
			
			int idx=0;
			for (String attr : updateAttrs) {
				switch (attr) {
				case Book.KEY_NAME:
					pst.setString(++idx, bk.getName());
					break;
				case Book.KEY_PUBLISHTIME:
					pst.setDate(++idx, new java.sql.Date(bk.getPublishTime().getTime()));
					break;
				case Book.KEY_AUTHOR:
					pst.setString(++idx, bk.getAuthor());
					break;
				case Book.KEY_PRICE:
					pst.setDouble(++idx, bk.getPrice());
					break;
				case Book.KEY_OLD:
					pst.setBoolean(++idx, !bk.getOld());
					break;
				case Book.KEY_PUBLISHER:
					pst.setString(++idx, bk.getPublisher());
					break;
				default:
					break;
				}
			}
			
			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		}
		return m;
	}

	private void decideUpdateAttrs(Book bk, List<String> updateAttrs,
			StringBuilder buf) {
		if(updateAttrs!=null && buf!=null){
			if(bk!=null){
				if( bk.getName()!=null ){
					updateAttrs.add(Book.KEY_NAME);
					buf.append( Book.getMappingMap().get(Book.KEY_NAME) );
				}
				if( bk.getPublishTime()!=null ){
					updateAttrs.add(Book.KEY_PUBLISHTIME);
					buf.append( Book.getMappingMap().get(Book.KEY_PUBLISHTIME) );
				}
				if( bk.getAuthor()!=null ){
					updateAttrs.add(Book.KEY_AUTHOR);
					buf.append( Book.getMappingMap().get(Book.KEY_AUTHOR) );
				}
				if( bk.getPrice()!=null ){
					updateAttrs.add(Book.KEY_PRICE);
					buf.append( Book.getMappingMap().get(Book.KEY_PRICE) );
				}
				if( bk.getOld() !=null ){
					updateAttrs.add(Book.KEY_OLD);
					buf.append( Book.getMappingMap().get(Book.KEY_OLD) );
				}
				if( bk.getPublisher()!=null ){
					updateAttrs.add(Book.KEY_PUBLISHER);
					buf.append( Book.getMappingMap().get(Book.KEY_PUBLISHER) );
				}
			}
		}
	}

	public Book get(Integer id) {
		Book bk=null;
		
		String sql = //"select * from customers where id=?";
				" select          " +
				" 	bk.id,        " +
				" 	bk.name,      " +
				" 	bk.pub_date,  " +
				" 	bk.author,    " +
				" 	bk.price,     " +
				" 	bk.is_new,    " +
				" 	bk.publisher  " +
				" from books bk   " +
				" where bk.id=?   " ;
				
		Connection con=null;
		PreparedStatement pst = null;
		ResultSet rs =null;
		try {
			con=DBUtil.createConnection();
			pst = con.prepareStatement(sql);
			
			pst.setInt(1, id);
			
			rs = pst.executeQuery();
			if (rs.next()) {
				bk=new Book();
				
				bk.setId( rs.getInt(1) );
				bk.setName( rs.getString(2) );
				bk.setPublishTime( new Date( rs.getDate(3).getTime() ) );
				bk.setAuthor( rs.getString(4) );
				bk.setPrice( rs.getDouble(5) );
				bk.setOld( !rs.getBoolean(6) );
				bk.setPublisher( rs.getString(7) );
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtil.close(null, pst, rs);
		}
		return bk;
	}

	public List<Book> getBooks() {
		List<Book> list=new ArrayList<Book>();
		String sql=
			" select         " +
			" 	bk.id        ," +
			" 	bk.name      ," +
			" 	bk.pub_date  ," +
			" 	bk.author    ," +
			" 	bk.price     ," +
			" 	bk.is_new    ," +
			" 	bk.publisher " +
			" from books bk  " ;

		Connection con=null;
		PreparedStatement pst = null;
		ResultSet rs =null;
		try {
			con=DBUtil.createConnection();
			pst = con.prepareStatement(sql);
			
			rs = pst.executeQuery();
			while (rs.next()) {
				
				Book bk=new Book();
				
				bk.setId( rs.getInt(1) );
				bk.setName( rs.getString(2) );
				bk.setPublishTime( new Date( rs.getDate(3).getTime() ) );
				bk.setAuthor( rs.getString(4) );
				bk.setPrice( rs.getDouble(5) );
				bk.setOld( !rs.getBoolean(6) );
				bk.setPublisher( rs.getString(7) );
				
				list.add(bk);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		}
		
		return list;
	}
	@Override
	public int add(List<Book> bks) {
		String sql =
				" insert into BOOKS                               "+
				" (id,name,pub_date,author,price,is_new,publisher)"+
				" values                                          "+
				" (book_id_seq.nextval,?,?,?,?,?,?)               ";
		Connection con=null;
		PreparedStatement pst = null;
		int m = 0;
		try {
			con=DBUtil.createConnection();
			con.setAutoCommit(false);
			
			for (Book bk : bks) {
				pst = con.prepareStatement(sql);
				
				//idx form 1
				pst.setString(1, bk.getName());
				pst.setDate(2, new java.sql.Date(bk.getPublishTime().getTime()));
				pst.setString(3, bk.getAuthor());
				pst.setDouble(4, bk.getPrice());
				pst.setBoolean(5, !bk.getOld());
				pst.setString(6, bk.getPublisher());
				pst.addBatch();
			}
			m = pst.executeBatch().length;
			con.commit();  
			con.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
//			DBUtil.close(null, pst, null);
			DBUtil.close(con, pst, null);
		}
		return m;
	}


}
